﻿using System.Collections.Generic;
using System.Text;
using DatingDAL;
using System.Xml;
using System.Data.SqlClient;
using System.Xml.Linq;
using System;

namespace SampleExamDatabases
{
    class DatingAgency
    {
        public static int personsCount = 0;
        public static int prefsCount = 0;
        const int FLUSH_NUMBER = 120;

        const string PEOPLE_FILE = "../../bigXml.xml";
        const string OUPUT_FILE = "../../datas.xml";

        public void FillInDatabase()
        {
            XElement xmlFile = XElement.Load(PEOPLE_FILE);

            StringBuilder insertPeople = new StringBuilder(10000);
            int peopleCounter = 0;
            
            insertPeople.Append("INSERT INTO PEOPLE(PersonId,Name,Age,HairColor,EyeColor,Height,Gender)VALUES");
            StringBuilder insertPreferences = new StringBuilder(30000);
            insertPreferences.Append("INSERT INTO PREFERENCES(FromHeight,ToHeight,FromAge,ToAge,HairColor,EyeColor,PersonID,Gender)VALUES");
            SqlConnection connection = new SqlConnection(@"Server=.\SQLEXPRESS; Database=DatingDatabase; Integrated Security=true");
            connection.Open();

            using (connection)
            {
                string getLastId = @"TRUNCATE TABLE PREFERENCES DELETE FROM People";
                SqlCommand clearTable = new SqlCommand(getLastId, connection);
                clearTable.ExecuteNonQuery();
                int personID = 0;

                foreach (var person in xmlFile.Descendants("person"))
                {
                    
                    string gender = person.Parent.Parent.Name.LocalName;
                    insertPeople.Append(string.Format("({0},'{1}',{2},'{3}','{4}',{5},'{6}'),",
                         personID,
                         person.Attribute("name").Value,
                         person.Attribute("age").Value,
                         person.Attribute("haircolor").Value,
                         person.Attribute("eyecolor").Value,
                         person.Attribute("height").Value,
                         gender
                         ));
                    foreach (var girl in person.Descendants("girl"))
                    {
                        CreatePreference(girl, personID, insertPreferences);
                    }

                    foreach (var boy in person.Descendants("guy"))
                    {
                        CreatePreference(boy, personID, insertPreferences);
                    }
                    peopleCounter++;
                    if (peopleCounter % FLUSH_NUMBER ==0)
                    {
                        InsertIntoDatabase(insertPeople, insertPreferences, connection);
                    }
                    personID++;
                }
                if (insertPeople.Length != 0)
                {
                    InsertIntoDatabase(insertPeople, insertPreferences, connection);
                }
            }
        }
  
        private void CreatePreference(XElement girl, int personID, StringBuilder insertPreferences)
        {
            int fromHeight;
            int toHeight;
            parseNumbers(girl.Element("height").Value, out fromHeight, out toHeight);
            int fromAge;
            int toAge;
            parseNumbers(girl.Element("age").Value, out fromAge, out toAge);

            insertPreferences.Append(string.Format("({0},{1},{2},{3},'{4}','{5}',{6},'girl'),",
                                                   fromHeight,
                                                   toHeight,
                                                   fromAge,
                                                   toAge,
                                                   girl.Element("haircolor").Value,
                                                   girl.Element("eyecolor").Value,
                                                   personID));
        }
  
        private void InsertIntoDatabase(StringBuilder insertPeople, StringBuilder insertPreferences, SqlConnection connection)
        {
            insertPeople.Remove(insertPeople.Length - 1, 1);
            insertPreferences.Remove(insertPreferences.Length - 1, 1);
            SqlCommand insertPeopleCommand = new SqlCommand("dbo.usp_InsertRecords @param1 EXEC dbo.usp_InsertRecords @param2", connection);
            insertPeopleCommand.Parameters.Add(new SqlParameter("@param1", insertPeople.ToString()));
            insertPeopleCommand.Parameters.Add(new SqlParameter("@param2", insertPreferences.ToString()));
            insertPeopleCommand.ExecuteNonQuery();

            //SqlCommand insertPreferencesCommand = new SqlCommand(insertPreferences.ToString(), connection);
            //insertPreferencesCommand.ExecuteNonQuery();
            insertPreferences.Clear();
            insertPeople.Clear();
            insertPeople.Append("INSERT INTO PEOPLE(PersonId,Name,Age,HairColor,EyeColor,Height,Gender)VALUES");
            insertPreferences.Append("INSERT INTO PREFERENCES(FromHeight,ToHeight,FromAge,ToAge,HairColor,EyeColor,PersonID,Gender)VALUES");
        }

        //public void FillInDatabase()
        //{
        //    DatingDatabaseEntities entity = new DatingDatabaseEntities();
        //    XmlReader reader = XmlReader.Create(PEOPLE_FILE);

        //    using (reader)
        //    {
        //        string gender = string.Empty;
        //        Person newPerson = null;
        //        while (reader.Read())
        //        {
        //            if (reader.NodeType == XmlNodeType.Element)
        //            {
        //                string currentTag = reader.Name;
        //                switch (currentTag)
        //                {
        //                    case "person":
        //                        {
        //                            newPerson = new Person();
        //                            string name = reader.GetAttribute("name");
        //                            int age = int.Parse(reader.GetAttribute("age"));
        //                            string hairColor = reader.GetAttribute("haircolor");
        //                            string eyeColor = reader.GetAttribute("eyecolor");
        //                            int height = int.Parse(reader.GetAttribute("height"));

        //                            //Console.WriteLine(name);
        //                            personsCount++;

        //                            newPerson.Name = name;
        //                            newPerson.Age = age;
        //                            newPerson.EyeColor = eyeColor;
        //                            newPerson.HairColor = hairColor;
        //                            newPerson.Height = height;
        //                            newPerson.Gender = gender;
                                   
        //                            entity.People.AddObject(newPerson);
                                    
        //                            break;
        //                        }
        //                    case "preferences":
        //                        {
        //                            List<Preference> preferences = GetPreferences(reader);
        //                            foreach (var pref in preferences)
        //                            {
        //                                newPerson.Preferences.Add(pref);
        //                            }
                                    
        //                            break;
        //                        }
        //                    case "guys":
        //                        {
        //                            gender = "guy";
        //                            break;
        //                        }
        //                    case "girls":
        //                        {
        //                            gender = "girl";
        //                            break;
        //                        }
        //                }
        //            }
        //        }
        //    }
        //    entity.SaveChanges();
        //}

        private List<Preference> GetPreferences(XmlReader reader)
        {
            List<Preference> preferences = new List<Preference>();
            ReadToElement(reader);

            while(reader.Name=="girl" || reader.Name=="guy")
            {
                
                string gender = reader.Name;
                ReadToElement(reader);
                int fromHeight;
                int toHeight;
                parseNumbers(reader.ReadElementString(), out fromHeight, out toHeight);
                ReadToElement(reader);
                string haircolor = reader.ReadElementString();
                ReadToElement(reader);
                string eyecolor = reader.ReadElementString();
                ReadToElement(reader);
                int fromAge;
                int toAge;
                parseNumbers(reader.ReadElementString(), out fromAge, out toAge);

                Preference newPref = new Preference()
                {
                    Gender = gender,
                    FromHeight = fromHeight,
                    ToHeight = toHeight,
                    FromAge = fromAge,
                    ToAge = toAge,
                    HairColor = haircolor,
                    EyeColor = eyecolor

                };
                preferences.Add(newPref);
                prefsCount++;
                ReadToElement(reader);
            }

            return preferences;
        }

        private void parseNumbers(string stringToParse, out int start, out int end)
        {
            string[] numbers = stringToParse.Split('-');
            start = int.Parse(numbers[0]);
            end = int.Parse(numbers[1]);
        }

        private void ReadToElement(XmlReader reader)
        {
            reader.Read();
           
            while (reader.NodeType != XmlNodeType.Element)
            {
                reader.Read();
                if (reader.Name == "preferences")
                {
                    break;
                }
            }
        }

        public List<string> FindPerfectMatches()
        {
            DatingDatabaseEntities entity = new DatingDatabaseEntities();
            List<string> matches = new List<string>();

            foreach (var person in entity.People.Include("Preferences"))
            {
                foreach (var pref in entity.Preferences)
                {
                    if (person.HairColor == pref.HairColor &&
                        person.EyeColor == pref.EyeColor &&
                        person.Gender == pref.Gender &&
                        person.Age >= pref.FromAge &&
                        person.Age <= pref.ToAge &&
                        person.Height >= pref.FromHeight &&
                        person.Height <= pref.ToHeight)
                    {
                        matches.Add(person.Name + "-" + pref.Person.Name);
                    }
                }
            }

            return matches;
        }

        public void writeResultToXml(List<string> matches)
        {
            XmlTextWriter writer = new XmlTextWriter(OUPUT_FILE, Encoding.UTF8);
            using (writer)
            {
                writer.Formatting = Formatting.Indented;
                writer.IndentChar = '\t';
                writer.Indentation = 1;
                writer.WriteStartDocument();
                writer.WriteStartElement("dates");

                foreach (var match in matches)
                {
                    string[] names = match.Split('-');
                      writer.WriteStartElement("date");
                      writer.WriteStartElement("person");
                      string firstPerson = names[0];
                      writer.WriteAttributeString("name", firstPerson);
                      writer.WriteEndElement();
                      string secondPerson = names[1];
                      writer.WriteStartElement("person");
                      writer.WriteAttributeString("name", secondPerson);
                      writer.WriteEndElement();
                      writer.WriteEndElement();
                }
                writer.WriteEndDocument();
            }
        }

        public void WriteResultsToDatesDatabase(List<string> matches)
        {
            SqlConnection connection = new SqlConnection(@"Server=.\SQL_SERVER_2008; Database=Dates; Integrated Security = true");
            connection.Open();

            using (connection)
            {
                string insertIntoTable = @"INSERT INTO Dates(FristPerson,SecondPerson)Values(@firstPerson,@secondPerson)";
                SqlCommand insertIntoTableCommand = new SqlCommand(insertIntoTable, connection);
                insertIntoTableCommand.Parameters.Add(new SqlParameter("@firstPerson", ""));
                insertIntoTableCommand.Parameters.Add(new SqlParameter("@secondPerson", ""));

                foreach (var match in matches)
                {
                    string[] names = match.Split('-');
                    insertIntoTableCommand.Parameters["@firstPerson"].Value = names[0];
                    insertIntoTableCommand.Parameters["@secondPerson"].Value = names[1];
                    insertIntoTableCommand.ExecuteNonQuery();
                }
            }
        }
    }
}
